package edu.uct.tamnd.viettourism.helper;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import edu.uct.tamnd.viettourism.atm.TableNameATM;
import edu.uct.tamnd.viettourism.atm.TableNameBank;
import edu.uct.tamnd.viettourism.catlist.CatItem;
import edu.uct.tamnd.viettourism.catlist.TableNameCat;
import edu.uct.tamnd.viettourism.citylist.CityItem;
import edu.uct.tamnd.viettourism.citylist.TableNameCity;
import edu.uct.tamnd.viettourism.places.PlacesItem;
import edu.uct.tamnd.viettourism.places.TableNamePlaces;

public class DatabaseHelper extends SQLiteOpenHelper {
	private static String TAG = "DBOpenHelper";
	private static String DB_PATH = "/data/data/edu.uct.tamnd.viettourism/databases/";
	private static String DB_NAME = "demo.db";

	private static int DB_VERSION = 1;
	private static SQLiteDatabase mDB;
	private static Context mContext;

	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		this.mContext = context;
	}


	public void createDatabase() throws IOException {
		boolean dbExist = checkDatabase();
		if (dbExist) {
			Log.d(TAG, "createDatabase: DB exist.");
			String myPath = DB_PATH + DB_NAME;
			File dbfile = new File(myPath);
			InputStream myInput = mContext.getAssets().open(DB_NAME);
			Log.d(TAG,
					"Size: " + dbfile.length() + " vs " + myInput.available());

			if (dbfile.length() == myInput.available()) {
				Log.d(TAG, "hai file giốngg nhau");
			} else {
				Log.d(TAG, "hai file khác nhau");
				dbfile.delete();
				Log.d(TAG, "xóa file db");
				this.getReadableDatabase();
				try {
					Log.d(TAG, "createDatabase: trước khi copy");
					copyDatabase();
					Log.d(TAG, "createDatabase: đã copy xong");
				} catch (IOException e) {
					throw new Error("Error copying database");
				}
			}

		} else {
			this.getReadableDatabase();
			try {
				Log.d(TAG, "createDatabase: trước khi copy");
				copyDatabase();
				Log.d(TAG, "createDatabase: đã copy xong");
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	private void copyDatabase() throws IOException {
		// Open your local db as the input stream
		InputStream myInput = mContext.getAssets().open(DB_NAME);

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public static boolean checkDatabase() {
		SQLiteDatabase checkDB = null;
		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {
			Log.d(TAG, e.getMessage());
		}
		if (checkDB != null) {
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	public static void openDatabase() throws SQLiteException {
		String myPath = DB_PATH + DB_NAME;
		mDB = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);

	}
//
//	public synchronized void close() {
//		if (mDB != null) {
//			mDB.close();
//		}
//		super.close();
//	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

	public static ArrayList<CityItem> readCity() {
		openDatabase();
		String SQLiteQuery = "SELECT * FROM " + TableNameCity.TABLE_NAME;
		Cursor cursor = mDB.rawQuery(SQLiteQuery, null);
		ArrayList<CityItem> arraylist = new ArrayList<CityItem>();
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				do {
					Integer id = cursor.getInt(cursor
							.getColumnIndex(TableNameCity.COLUMN_ID));
					String name = cursor.getString(cursor
							.getColumnIndex(TableNameCity.COLUMN_NAME));
					String image = cursor.getString(cursor
							.getColumnIndex(TableNameCity.COLUMN_IMAGE));
					String description = cursor.getString(cursor
							.getColumnIndex(TableNameCity.COLUMN_DES));

					CityItem item = new CityItem(id, name, description, image);
					arraylist.add(item);

				} while (cursor.moveToNext());
			}
		}
		return arraylist;
	}

	public static ArrayList<CatItem> readCategory(int cityID) {
		// TODO Auto-generated method stub
		openDatabase();
		// String SQLiteQuery = "SELECT * FROM " + TableNameCat.TABLE_NAME;
		String SQLiteQuery = "SELECT DISTINCT a.* FROM "
				+ TableNameCat.TABLE_NAME + " a, " + TableNamePlaces.TABLE_NAME
				+ " b WHERE a." + TableNameCat.COLUMN_ID + " = b."
				+ TableNamePlaces.COLUMN_CAT_ID + " AND "
				+ TableNamePlaces.COLUMN_CITY_ID + " = " + cityID;
		Cursor cursor = mDB.rawQuery(SQLiteQuery, null);
		ArrayList<CatItem> arraylist = new ArrayList<CatItem>();
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				do {
					Integer id = cursor.getInt(cursor
							.getColumnIndex(TableNameCat.COLUMN_ID));
					String name = cursor.getString(cursor
							.getColumnIndex(TableNameCat.COLUMN_NAME));
					String image = cursor.getString(cursor
							.getColumnIndex(TableNameCat.COLUMN_IMAGE));
					String description = cursor.getString(cursor
							.getColumnIndex(TableNameCat.COLUMN_DES));

					CatItem item = new CatItem(id, name, description, image);
					arraylist.add(item);

				} while (cursor.moveToNext());
			}
		}
		return arraylist;
	}

	public static ArrayList<PlacesItem> readPlaces(int cityID, int catID) {
		// TODO Auto-generated method stub
		openDatabase();
		String SQLiteQuery = "SELECT * FROM " + TableNamePlaces.TABLE_NAME
				+ " WHERE " + TableNamePlaces.COLUMN_CITY_ID + " = " + cityID
				+ " AND " + TableNamePlaces.COLUMN_CAT_ID + " = " + catID;
		Cursor cursor = mDB.rawQuery(SQLiteQuery, null);
		ArrayList<PlacesItem> arraylist = new ArrayList<PlacesItem>();
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				do {
					Integer id = cursor.getInt(cursor
							.getColumnIndex(TableNamePlaces.COLUMN_ID));
					String name = cursor.getString(cursor
							.getColumnIndex(TableNamePlaces.COLUMN_NAME));
					String image = cursor.getString(cursor
							.getColumnIndex(TableNamePlaces.COLUMN_IMAGE));
					String description = cursor.getString(cursor
							.getColumnIndex(TableNamePlaces.COLUMN_DES));

					PlacesItem item = new PlacesItem(id, name, description,
							image);
					arraylist.add(item);

				} while (cursor.moveToNext());
			}
		}
		return arraylist;
	}

	public Cursor readPlacesDetail(int placesID) {
		this.getReadableDatabase();
		openDatabase();
		String sql = "SELECT * FROM " + TableNamePlaces.TABLE_NAME + " WHERE "
				+ TableNamePlaces.COLUMN_ID + " = " + placesID;
		Cursor cursor = mDB.rawQuery(sql, null);
		return cursor;
	}

	public Cursor readPlacesMarker(String placesID) {
		this.getReadableDatabase();
		openDatabase();
		String sql = "SELECT * FROM " + TableNamePlaces.TABLE_NAME + " WHERE "
				+ TableNamePlaces.COLUMN_ID + " = " + placesID;
		Cursor cursor = mDB.rawQuery(sql, null);
		return cursor;
	}

	public Cursor readPlacesDistance() {
		// TODO Auto-generated method stub

		this.getReadableDatabase();
		openDatabase();
		String sql = "SELECT * FROM " + TableNamePlaces.TABLE_NAME;
		Cursor cursor = mDB.rawQuery(sql, null);
		return cursor;
	}

	public Cursor readCategory() {
		// TODO Auto-generated method stub

		this.getReadableDatabase();
		openDatabase();
		String sql = "SELECT * FROM " + TableNameCat.TABLE_NAME;
		Cursor cursor = mDB.rawQuery(sql, null);
		return cursor;
	}

	public Cursor readIDCategory(String catName) {
		// TODO Auto-generated method stub

		this.getReadableDatabase();
		openDatabase();
		String sql = "SELECT " + TableNameCat.COLUMN_ID + " FROM "
				+ TableNameCat.TABLE_NAME + " WHERE "
				+ TableNameCat.COLUMN_NAME + " = " + "'" + catName + "'";
		Cursor cursor = mDB.rawQuery(sql, null);
		return cursor;
	}
	
	public Cursor readBank(String bankID) {
		// TODO Auto-generated method stub

		this.getReadableDatabase();
		openDatabase();
		String sql = "SELECT * FROM " + TableNameBank.TABLE_NAME 
				+ " WHERE " + TableNameBank.COLUMN_ID + " = " + bankID;
		Cursor cursor = mDB.rawQuery(sql, null);
		return cursor;
	}
	
	public Cursor readATM(String atmID) {
		this.getReadableDatabase();
		openDatabase();
		String sql = "SELECT * FROM " + TableNameATM.TABLE_NAME + " WHERE "
				+ TableNameATM.COLUMN_ID + " = " + atmID;
		Cursor cursor = mDB.rawQuery(sql, null);
		return cursor;
	}
	
	public Cursor readPlacesATM(String bankID) {
		this.getReadableDatabase();
		openDatabase();
		String sql = "SELECT * FROM " + TableNameATM.TABLE_NAME + " WHERE "
				+ TableNameATM.COLUMN_BANK_ID + " = " + bankID;
		Cursor cursor = mDB.rawQuery(sql, null);
		return cursor;
	
	}

	public Cursor readPlacesATM() {
		this.getReadableDatabase();
		openDatabase();
		String sql = "SELECT * FROM " + TableNameATM.TABLE_NAME;
		Cursor cursor = mDB.rawQuery(sql, null);
		return cursor;
	}
	
}
